import hashlib
import os.path
import re

from flask_jwt_extended import (create_access_token, create_refresh_token, jwt_required, get_jwt_identity, get_jwt)


from datamp.user.models import *
from datamp.data.models import *
from ext import db
from werkzeug.utils import secure_filename
from werkzeug.datastructures import FileStorage
from settings import Config
import pymysql
import werkzeug
from flask import Blueprint, request, render_template, redirect, url_for, jsonify, session, g, make_response, send_file, \
    Response, send_from_directory
from flask_restful import Api, Resource, reqparse, inputs, fields, marshal, marshal_with
from sqlalchemy import or_, and_
from werkzeug.security import generate_password_hash, check_password_hash

data_bp = Blueprint('data', __name__, url_prefix='/data', template_folder='templates')

required_login_list = ['/data/manage', '/data/setting', '/data/models']

api = Api(data_bp)

basic_data_parser = reqparse.RequestParser()
basic_data_parser.add_argument('school_name', type=str, help='学校名字不对', location=['form', 'args', 'json', 'files'])

default_classification_parser = reqparse.RequestParser()
default_classification_parser.add_argument('classification_name', type=str, help='分类体系名字不对', location=['form', 'args', 'json', 'files'])
default_classification_parser.add_argument('dimension_name', type=str, help='分类名字不对', location=['form', 'args', 'json', 'files'])

behavioral_data_sheet_register_parser = reqparse.RequestParser()
behavioral_data_sheet_register_parser.add_argument('behavioral_data_sheet_name', type=str, help='行为数据表名字不对',
                                                location=['form', 'args', 'json', 'files'])
behavioral_data_sheet_register_parser.add_argument('behavioral_name', type=str, help='行为数据名称不对',
                                                location=['form', 'args', 'json', 'files'])


# 钩子函数
@data_bp.before_app_first_request
def first_request():
    print('before_app_first_request')


# 钩子函数
@data_bp.before_app_request
def before_request1():
    print('before_app_request', request.path)
    if request.path in required_login_list:
        id = session.get('uid')
        if not id:
            return render_template('login.html')
        else:
            user = UserInfo.query.get(id)
            # g对象，本次请求的对象
            g.user = user


# ------数据服务管理------
# 1. 基础数据管理
@data_bp.route('/data/manage')
def data_manage():
    return render_template("manage.html")


# --根据学校名称查询--
# class BasicData(Resource):
#     def post(self):
#         args = basic_data_parser.parse_args()
#         school_name = args.get('school_name')
#         # results = TeacherandStuBehavior.query.join(TeacherandStu,TeacherandStuBehavior.teacherandstu_id==TeacherandStu.id).join(Schoolinformation,TeacherandStu.schoolinformation_id==Schoolinformation.id).filter(Schoolinformation.schoolname=='A').all()
#         results = TeacherandStuBehavior.query.join(TeacherandStu).join(Schoolinformation).filter(
#             Schoolinformation.schoolname == school_name).all()
#         print('results:', results)
#         load_list = []
#         data_dict = {}
#         for result in results:
#             data_serialize = result.test_schema()
#             aa = {'data': data_serialize}
#             data_dict.update(aa)
#             load_list.append(data_dict)
#             data_dict = {}
#         print('data_dict:', data_dict)
#         print('load_list:', load_list)
#         return jsonify(load_list)
#
#
# api.add_resource(BasicData, '/BasicData')


# --行为数据表注册--
class BehavioralDataSheetRegister(Resource):
    def post(self):
        args = behavioral_data_sheet_register_parser.parse_args()
        behavioraldatasheetname = args.get('behavioraldatasheetname')
        behavioralname = args.get('behavioralname')
        suffix = re.split(r'[,，.]', behavioralname)
        print(suffix)
        db2 = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask07", charset="utf8",
                              autocommit=True)
        cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)

        def create_table(tbl_name):
            '''新建数据表'''
            sql = 'create table if not exists %s(id int PRIMARY KEY NOT NULL AUTO_INCREMENT UNIQUE, behavioraldataname VARCHAR(50) UNIQUE)' % tbl_name
            cursor.execute(sql)

        create_table(behavioraldatasheetname)
        li = []
        for i in suffix:
            li.append(("%s" % i))
        sql = "INSERT IGNORE INTO {0}(behavioraldataname) VALUES (%s)".format(behavioraldatasheetname)
        cursor.executemany(sql, li)
        db2.commit()
        # sql="alter table {0} drop id".format(systemname)
        # cursor.execute(sql)
        # sql="alter table {0} add id bigint primary key not null auto_increment first ".format(systemname)
        # cursor.execute(sql)
        behavioraldatasheetnames = BehavioralDataSheet.query.filter(
            BehavioralDataSheet.behavioraldatasheetname == behavioraldatasheetname).all()
        if behavioraldatasheetnames:
            return '存在同名行为数据表，已经在同名行为数据表中更新行为数据名称'
        else:
            behavioraldatasheet = BehavioralDataSheet()
            behavioraldatasheet.behavioraldatasheetname = behavioraldatasheetname
            db.session.add(behavioraldatasheet)
            db.session.commit()
            return '成功'


api.add_resource(BehavioralDataSheetRegister, '/BehavioralDataSheetRegister')


# --行为数据表和行为数据名称返回前端--
class behavioraldatasheet(Resource):
    @jwt_required()
    def get(self):
        db2 = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask07", charset="utf8",
                              autocommit=True)
        cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)
        # sql = "select * from behavioraldatasheet;"
        # cursor.execute(sql)
        res = cursor.fetchall()
        return jsonify(res)

    def post(self):
        args = behavioral_data_sheet_register_parser.parse_args()
        behavioraldatasheetname = args.get('behavioraldatasheetname')
        suffix = re.split(r'[,，.]', behavioraldatasheetname)
        print(suffix)
        db2 = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask07", charset="utf8",
                              autocommit=True)
        cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)
        loadapplist = []
        dataapp = {}
        loadapplist2 = []
        for i in suffix:
            sql = "select * from %s;" % i
            cursor.execute(sql)
            res2 = cursor.fetchall()
            dataapp['behavioraldatasheetname'] = i
            dataapp['behavioraldataname'] = res2
            loadapplist.append(dataapp)
            dataapp = {}
            loadapplist2.append(loadapplist)
            loadapplist = []
        print(loadapplist2)
        return jsonify(loadapplist2)


api.add_resource(behavioraldatasheet, '/behavioraldatasheet')


# --分类体系注册和返回前端--
class DefaultClassificationDimension(Resource):
    def post(self):
        args = default_classification_parser.parse_args()
        classification_name = args.get('classification_name')
        dimension_name = args.get('dimension_name')
        suffix = re.split(r'[,，.]', dimension_name)
        print(suffix)
        db2 = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask07", charset="utf8",
                              autocommit=True)
        cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)

        def create_table(tbl_name):
            '''新建数据表'''
            sql = 'create table if not exists %s(id int PRIMARY KEY NOT NULL AUTO_INCREMENT UNIQUE, ' \
                  'dimension_name VARCHAR(50) UNIQUE)' % tbl_name
            cursor.execute(sql)

        create_table(classification_name)
        li = []
        for i in suffix:
            li.append(("%s" % i))
        sql = "INSERT IGNORE INTO {0}(dimension_name) VALUES (%s)".format(classification_name)
        cursor.executemany(sql, li)
        db2.commit()
        # id重新从1开始计算
        # sql="alter table {0} drop id".format(systemname)
        # cursor.execute(sql)
        # sql="alter table {0} add id bigint primary key not null auto_increment first ".format(systemname)
        # cursor.execute(sql)
        classification_names = DefaultClassification.query.filter(DefaultClassification.classification_name == classification_name).all()
        if classification_names:
            return '存在同名分类体系，已经在同名分类体系中更新分类名称'
        else:
            default_classification = DefaultClassification()
            default_classification.classification_name = classification_name
            db.session.add(default_classification)
            db.session.commit()
            return '成功'

    def get(self):
        db2 = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask07", charset="utf8",
                              autocommit=True)
        cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)
        # sql = "select * from default_classification;"
        # cursor.execute(sql)
        res = cursor.fetchall()

        load_list = []
        data_dict = {}
        load_list2 = []
        for i in res:
            dimension_name = i['classification_name']
            sql = "select * from %s;" % dimension_name
            cursor.execute(sql)
            res2 = cursor.fetchall()
            data_dict['classification_name'] = dimension_name
            data_dict['dimension_name'] = res2
            load_list.append(data_dict)
            data_dict = {}
            load_list2.append(load_list)
            load_list = []
        print(load_list2)
        return jsonify(load_list2)


api.add_resource(DefaultClassificationDimension, '/DefaultClassificationDimension')


# 2. 教学数据服务
@data_bp.route('/data/setting')
def data_setting():
    return render_template("setting.html")


# 3. 教学类模型服务
@data_bp.route('/data/models')
def data_models():
    return render_template("models.html")
